﻿using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using System.Data.SqlClient;
using System.Data;

namespace OgilvyOne.SqlServerDAL
{
   public class MemberPassBackDal:IMemberPassBack
    {
       string connstr = SQLHelper.CONN_STRING_NON_DTC;
        #region IMemberPassBack 成员

        public int Add(MemberPassBackModel model)
        {
            string str = "insert into COFCO_PASS_BACK(MEMBER_ID,KEYPARMS,STATE) values(@MEMBER_ID,@KEYPARMS,@STATE);select @@IDENTITY";
            SqlParameter[] parms = new SqlParameter[] {
               new SqlParameter("@MEMBER_ID",SqlDbType.Int),
               new SqlParameter("@KEYPARMS",SqlDbType.NVarChar,50),
               new SqlParameter("@STATE",SqlDbType.Int)
            };
            parms[0].Value = model.MemberId;
            parms[1].Value = model.Keyparms;
            parms[2].Value = model.State;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, str, parms);                 
        }

        public bool Update(MemberPassBackModel model)
        {
            string str = "update COFCO_PASS_BACK set KEYPARMS=@KEYPARMS,STATE=@STATE where MEMBER_ID=@MEMBER_ID";
            SqlParameter[] parms = new SqlParameter[] {
               new SqlParameter("@MEMBER_ID",SqlDbType.Int),
               new SqlParameter("@KEYPARMS",SqlDbType.NVarChar,50),
               new SqlParameter("@STATE",SqlDbType.Int)
            };
            parms[0].Value = model.MemberId;
            parms[1].Value = model.Keyparms;
            parms[2].Value = model.State;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, str, parms)>0;
        }

        public bool Delete(int MEMBER_ID)
        {
            string str = "delete from COFCO_PASS_BACK where MEMBER_ID=@MEMBER_ID";
            SqlParameter[] parms = new SqlParameter[] {
               new SqlParameter("@MEMBER_ID",SqlDbType.Int)
            };
            parms[0].Value =MEMBER_ID;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, str, parms) > 0;
        }

        public bool Exist(int MEMBER_ID)
        {
            string str = "select * from COFCO_PASS_BACK where MEMBER_ID=@MEMBER_ID";
            SqlParameter[] parms = new SqlParameter[] {
               new SqlParameter("@MEMBER_ID",SqlDbType.Int)
            };
            parms[0].Value = MEMBER_ID;
            return int.Parse(SQLHelper.ExecuteScalar(connstr, CommandType.Text, str, parms).ToString()) > 0;
        }

        public MemberPassBackModel Getmodel(int MEMBER_ID)
        {
            MemberPassBackModel model = new MemberPassBackModel();
            string str = "select * from COFCO_PASS_BACK where MEMBER_ID=@MEMBER_ID";
            SqlParameter[] parms = new SqlParameter[] {
               new SqlParameter("@MEMBER_ID",SqlDbType.Int)
            };
            parms[0].Value = MEMBER_ID;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(connstr, CommandType.Text, str, parms))
            {
                if (rdr.Read())
                {                  
                    
                    if (!rdr.IsDBNull(0)) model.Id = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) model.MemberId = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) model.Keyparms = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) model.State = rdr.GetInt32(3);                  
                }
            }
            return model;
        }

        #endregion
    }
}
